package com.cjack.frenchexcel.service;

import cn.hutool.core.io.FileUtil;
import cn.hutool.core.text.csv.CsvData;
import cn.hutool.core.text.csv.CsvReader;
import cn.hutool.core.text.csv.CsvRow;
import cn.hutool.core.text.csv.CsvUtil;
import cn.hutool.core.util.ReUtil;
import com.cjack.frenchexcel.config.LayPage;
import com.cjack.frenchexcel.dao.QontoDao;
import com.cjack.frenchexcel.dao.SystemparamDao;
import com.cjack.frenchexcel.exception.CommonException;
import com.cjack.frenchexcel.model.QontoModel;
import com.cjack.frenchexcel.table.QontoTable;
import com.cjack.frenchexcel.table.SystemparamTable;
import com.cjack.frenchexcel.util.DateFormatUtil;
import com.cjack.frenchexcel.util.FileUtils;
import com.cjack.frenchexcel.util.HttpClientUtil;
import com.cjack.frenchexcel.util.ModelUtils;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.io.FileNotFoundException;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 对接qonto平台，查询交易数据
 */
@Service
public class QontoService {
    private final static Logger LOG = LogManager.getLogger( QontoService.class);

    @Autowired
    private QontoDao qontoDao;
    @Autowired
    private SystemparamDao systemparamDao;

    @Value( "${qonto.getTransactionsUrl}")
    private String getTransactionsUrl;
    @Value( "${qonto.account}")
    private String account;
    @Value( "${qonto.key}")
    private String key;
    @Value( "${qonto.slug}")
    private String slug;
    @Value( "${qonto.iban}")
    private String iban;
    @Value("${file.upload.baseServerDir}")
    String baseServerDir;
    @Value("${file.upload.baseClientDir}")
    String baseClientDir;
    @Value("${file.upload.spelitor}")
    String spelitor;

    public Page< QontoTable> list( LayPage page, QontoModel condition) {
        Specification< QontoTable> specification = handleConditon( condition);
        List< Sort.Order> orders=new ArrayList<>();
        orders.add( new Sort.Order( Sort.Direction.DESC, "emittedAt"));
        Pageable pageable = new PageRequest( page.getPage()-1, page.getLimit(), new Sort( orders));
        Page< QontoTable> resultPage = qontoDao.findAll( specification, pageable);
        return resultPage;
    }

    /**
     * 需要要求时间戳的格式
     * @param startTime 2019-11-18T10:43:29.000Z
     * @param endTime 2019-11-18T10:43:29.000Z
     * @throws Exception
     */
    public void getTransactionsFromQonto( String startTime, String endTime) throws Exception {
        String url = getTransactionsUrl + "?slug=" + slug + "&iban=" + iban;
        url += "&status[]=completed&status[]=declined&status[]=reversed&status[]=pending";
        if( !StringUtils.isEmpty( startTime)){
            url += "&settled_at_from=" + startTime;
        }
        if( !StringUtils.isEmpty( endTime)){
            url += "&settled_at_to=" + endTime;
        }
        JSONObject header = new JSONObject();
        header.put( "authorization", "" + account + ":" + key + "");
        LOG.info( "getTransactionsFromQonto,url-------------------------------" + url);
        LOG.info( "getTransactionsFromQonto,header-------------------------------" + header.toString());
        String response = HttpClientUtil.httpGet( url, header);
        LOG.info( "getRechargeQrCode-response-------------------------------" + response);
        JSONObject responseJson = JSONObject.fromObject( response);
        JSONArray transactionsArray = responseJson.getJSONArray( "transactions");
        if( transactionsArray != null && transactionsArray.size() != 0){
            List<QontoTable> transactions = new ArrayList<>();
            for( int i = 0 ; i< transactionsArray.size() ; i++){
                JSONObject j = transactionsArray.getJSONObject( i);
                QontoTable table = new QontoTable();
                table.setTransactionId( j.getString( "transaction_id") != "null" ? j.getString( "transaction_id") : "");
                table.setAmount( j.getString( "amount") != "null" ? j.getDouble( "amount") : 0.00);
                table.setAmountCents( j.getString( "amount_cents") != "null" ? j.getInt( "amount_cents") : 0);
                table.setAttachmentIds( j.getJSONArray( "attachment_ids").toString());
                table.setLocalAmount( j.getString( "local_amount") != "null" ? j.getDouble( "local_amount") : 0.00);
                table.setLocalAmountCents( j.getString( "local_amount_cents") != "null" ? j.getInt( "local_amount_cents") : 0);
                table.setSide( j.getString( "side") != "null" ? j.getString( "side") : "");
                table.setOperationType( j.getString( "operation_type") != "null" ? j.getString( "operation_type") : "");
                table.setCurrency( j.getString( "currency") != "null" ? j.getString( "currency") : "");
                table.setLocalCurrency( j.getString( "local_currency") != "null" ? j.getString( "local_currency") : "");
                table.setLabel( j.getString( "label") != "null" ? j.getString( "label") : "");
                if( !"null".equals( j.getString( "settled_at"))){
                    table.setSettledAt( DateFormatUtil.format( j.getString( "settled_at"), DateFormatUtil.DATE_RULE_3));
                }

                if( !"null".equals( j.getString( "emitted_at"))){
                    table.setEmittedAt( DateFormatUtil.format( j.getString( "emitted_at"), DateFormatUtil.DATE_RULE_3));
                }

                if( !"null".equals( j.getString( "updated_at"))){
                    table.setUpdatedAt( DateFormatUtil.format( j.getString( "updated_at"), DateFormatUtil.DATE_RULE_3));
                }
                table.setStatus( j.getString( "status") != "null" ? j.getString( "status") : "");
                table.setNote( j.getString( "note") != "null" ? j.getString( "note") : "" );
                table.setReference( j.getString( "reference") != "null" ? j.getString( "reference") : "");
                table.setVatAmount( j.getString( "vat_amount") != "null" ? j.getDouble( "vat_amount") : 0.00);
                table.setVatAmountCents( j.getString( "vat_amount_cents") != "null" ? j.getInt( "vat_amount_cents") : 0);
                table.setVatRate( j.getString( "vat_rate") != "null" ? j.getDouble( "vat_rate") : 0.00);
                table.setInitiatorId( j.getString( "initiator_id") != "null" ? j.getString( "initiator_id") : "" );
                table.setLabelIds( j.getString( "label_ids") != "null" ? j.getJSONArray( "label_ids").toString() : "");
                table.setAttachmentLost( j.getString( "attachment_lost") != "null" ? j.getBoolean( "attachment_lost") : false);
                table.setAttachmentRequired( j.getString( "attachment_required") != "null" ? j.getBoolean( "attachment_required") : false);
                transactions.add( table);
            }
            saveTransactions( transactions);
        }
    }

    //要做去重复处理，已知每天的交易数据量不大，这里将直接在保存每条数据的时候去数据库查询这条数据是否存在即可
    private void saveTransactions( List<QontoTable> transactions){
        if( transactions == null || transactions.size() == 0){
            return;
        }
        for( QontoTable transaction : transactions){
            String transactionId = transaction.getTransactionId();
            QontoTable t = qontoDao.findByTransactionId( transactionId);
            //防止数据重复
            if( t == null){
                qontoDao.save( transaction);
            }
        }
    }

    public void add( QontoModel model){
        QontoTable table;
        if( model.getId() == null){
            table = ModelUtils.copySignModel( model, QontoTable.class);
        }else{
            table = qontoDao.findOne( model.getId());
            ModelUtils.copySignModel( model, table);
        }
        qontoDao.save( table);
    }

    public QontoModel getLastById( Long id){
        QontoTable table = qontoDao.getLastById( id);
        if( table == null){
            return null;
        }
        return ModelUtils.copySignModel( table, QontoModel.class);
    }

    public Double getAccountTotal(){
        Double total = 0.00;
        //查询系统参数里面的账户初始余额
        SystemparamTable systemparamTable = systemparamDao.getFirstByIdNotNull();
        Double debitTotal = qontoDao.sumBySide( "debit");//出账总额
        Double creditTotal = qontoDao.sumBySide( "credit");//入账总额
        if( debitTotal == null){
            debitTotal = 0.00;
        }
        if( creditTotal == null){
            creditTotal = 0.00;
        }
        if( systemparamTable.getQontoBaseCount() == null){
            systemparamTable.setQontoBaseCount( 0.00);
        }
        DecimalFormat df =new DecimalFormat("#.00");
        total = Double.valueOf( df.format( new BigDecimal( systemparamTable.getQontoBaseCount()).add( new BigDecimal( Math.abs( creditTotal))).subtract( new BigDecimal( Math.abs( debitTotal))).doubleValue()));
        return total;
    }

    /**
     *
     * @param filePath  excel的路径
     * @return  返回因为数据库重复而导入失败的数据
     * @throws CommonException
     * @throws FileNotFoundException
     */
    public JSONObject importFromExcel( String filePath) throws CommonException {
        //需要先将 excel的浏览器端访问路径 转化为服务器端访问路径
        filePath = FileUtils.transferClientFileUrl( filePath, baseClientDir, baseServerDir);
        CsvReader reader = CsvUtil.getReader();
        //从文件中读取CSV数据
        CsvData data = reader.read( FileUtil.file( filePath));
        List<CsvRow> rows = data.getRows();
        List<QontoModel> qontoModels = new ArrayList<>();
        Integer repeatCount = 0;
        //批量设置修改/新增时间
        //csv文件里面的数据从第六行开始，但是由于huttols导入的时候会自动去掉有一行空的内容，所以这里从5行开始读取
        for( int i = 0 ; i< rows.size() ; i++){
            //有效内容，格式为 02/03/20;0203202020200302-11.05.00.439527 -;VIR SEPA KITAKAMI LTD;;+9960
            String content = rows.get( i).get( 0);
            if( !StringUtils.isEmpty( content)){
                String date = content.split( ";")[0];
                String uniqueSign = content.split( ";")[1].split( " ")[0];
                //检查日期格式是否正确，来确定是否是一条有效的数据
                String regRule = "\\d{2}/\\d{2}/\\d{2,4}";
                if( !ReUtil.isMatch( regRule, date)){
                    LOG.info( "qonto导入csv数据，数据：[" + content + "]校验无效，不导入");
                    continue;
                }
                //判断数据是否已经导入
                QontoTable exsit = qontoDao.getByUniqueSign( uniqueSign);
                if( exsit != null){
                    LOG.info( "qonto导入csv数据，数据：[" + content + "]已经导入，不导入，根据uniqueSign：" + uniqueSign);
                    repeatCount++;
                    continue;
                }
                String decStr = rows.get( i).get( 1).split( ";")[0];
                QontoModel qontoModel = new QontoModel();
                if( !StringUtils.isEmpty( data)){
                    date = DateFormatUtil.formatSpecial( date, DateFormatUtil.DATE_RULE_3);
                }
                qontoModel.setEmittedAt( date);
                qontoModel.setLabel( content.split( ";")[2]);
                qontoModel.setAmount( Float.valueOf( content.split( ";")[ content.split( ";").length -1] + "." + decStr));
                qontoModel.setSide( qontoModel.getAmount() > 0 ? "credit" : "debit");
                qontoModel.setCreateTime( new Date());
                qontoModel.setUpdateTime( new Date());
                qontoModel.setUniqueSign( uniqueSign);
                qontoModels.add( qontoModel);
            }
        }
        qontoDao.save( ModelUtils.copyListModel( qontoModels, QontoTable.class));
        JSONObject json = new JSONObject();
        json.put( "repeatCount", repeatCount);
        json.put( "successCount", qontoModels.size());
        return json;
    }

    public static void main(String[] args) {
        System.out.println( DateFormatUtil.format( "05/06/20", DateFormatUtil.DATE_RULE_3));
    }

    /**
     * 组装查询条件
     * @param condition
     * @return
     */
    private Specification<QontoTable> handleConditon(QontoModel condition){
        Specification< QontoTable> specification = new Specification< QontoTable>() {
            @Override
            public Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder cb) {
                Predicate predicate = cb.conjunction();
                //起始日期
                if ( !StringUtils.isEmpty( condition.getEmittedAtStart())) {
                    predicate.getExpressions().add( cb.greaterThanOrEqualTo( root.get( "settledAt").as( String.class), condition.getEmittedAtStart()));
                }
                //结束日期
                if ( !StringUtils.isEmpty( condition.getEmittedAtEnd())) {
                    predicate.getExpressions().add( cb.lessThanOrEqualTo( root.get( "settledAt").as( String.class), condition.getEmittedAtEnd()));
                }
                return predicate;
            }
        };
        return specification;
    }
}
